package cn.edu.nju.software.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.sql.DataSource;

import cn.edu.nju.software.bean.show.BookItemInOrder;
import cn.edu.nju.software.bean.show.OrderSearchListItem;
import cn.edu.nju.software.bean.storage.Order;
import cn.edu.nju.software.dao.intf.OrderDao;
import cn.edu.nju.software.util.Constants;

public class OrderDaoImpl implements OrderDao {

	private DataSource ds;

	public OrderDaoImpl() {
		try {
			ds = JDBCUtil.getDataSource();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Order getOrderById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Order order = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT user_id, total_charge, status, "
							+ "order_addr, submit_time, disp_time, conf_time FROM bmorder "
							+ "WHERE id = ?");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				order = new Order(id, rs.getInt(1), rs.getBigDecimal(2),
						rs.getInt(3), rs.getString(4), rs.getTimestamp(5),
						rs.getTimestamp(6), rs.getTimestamp(7));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return order;
	}

	@Override
	public int submitOrder(Order order, List<BookItemInOrder> bookList)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int orderID = 0;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(
					"INSERT INTO bmorder (user_id, total_charge, "
							+ "order_addr) VALUES (?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			pstmt.setInt(1, order.getUserID());
			pstmt.setBigDecimal(2, order.getCharge());
			pstmt.setString(3, order.getAddress());
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				orderID = rs.getInt(1);
			}
			pstmt.close();
			pstmt = con
					.prepareStatement("INSERT INTO order_book (order_id, book_id, "
							+ "quantity, price) VALUES (?, ?, ?, ?)");
			for (BookItemInOrder item : bookList) {
				pstmt.setInt(1, orderID);
				pstmt.setInt(2, item.getBook().getBookID());
				pstmt.setInt(3, item.getQuantity());
				pstmt.setBigDecimal(4, item.getPrice());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return orderID;
	}

	@Override
	public boolean cancelOrder(int orderID) throws SQLException {
		// TODO Auto-generated method stub
		boolean result = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("UPDATE bmorder SET status = ? "
					+ "WHERE id = ? AND status = ?");
			pstmt.setInt(1, Constants.ORDER_CANCELED);
			pstmt.setInt(2, orderID);
			pstmt.setInt(3, Constants.ORDER_SUBMITTED);
			int modifiedRowCount = pstmt.executeUpdate();
			if (modifiedRowCount == 1) {
				result = true;
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public boolean dispatchOrder(Order order) throws SQLException {
		// TODO Auto-generated method stub
		boolean result = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmorder SET status = ?, disp_time = ? "
							+ "WHERE id = ? AND status = ?");
			pstmt.setInt(1, Constants.ORDER_DISPATCHED);
			pstmt.setTimestamp(2, order.getDispatchTime());
			pstmt.setInt(3, order.getOrderID());
			pstmt.setInt(4, Constants.ORDER_SUBMITTED);
			int modifiedRowCount = pstmt.executeUpdate();
			if (modifiedRowCount == 1) {
				result = true;
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public boolean confirmOrder(Order order) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmorder SET status = ?, conf_time = ? "
							+ "WHERE id = ? AND status = ?");
			pstmt.setInt(1, Constants.ORDER_CONFIRMED);
			pstmt.setTimestamp(2, order.getConfirmTime());
			pstmt.setInt(3, order.getOrderID());
			pstmt.setInt(4, Constants.ORDER_DISPATCHED);
			int modifiedRowCount = pstmt.executeUpdate();
			if (modifiedRowCount != 1) {
				return false;
			}
			pstmt.close();
			List<Integer> bookIDList = new ArrayList<Integer>();
			List<Integer> quantityList = new ArrayList<Integer>();
			pstmt = con
					.prepareStatement("SELECT book_id, quantity FROM order_book "
							+ "WHERE order_id = ?");
			pstmt.setInt(1, order.getOrderID());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				bookIDList.add(rs.getInt(1));
				quantityList.add(rs.getInt(2));
			}
			rs.close();
			pstmt.close();
			pstmt = con
					.prepareStatement("UPDATE book SET sales = sales + ? WHERE id = ?");
			for (int i = 0; i < bookIDList.size(); ++i) {
				pstmt.setInt(1, quantityList.get(i));
				pstmt.setInt(2, bookIDList.get(i));
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return true;
	}

	@Override
	public List<OrderSearchListItem> searchOrders(Map<Integer, Object> criteria)
			throws SQLException {
		// TODO Auto-generated method stub
		List<OrderSearchListItem> list = new ArrayList<OrderSearchListItem>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder(
				"SELECT u.act_name, o.id, o.user_id, "
						+ "o.total_charge, o.status, o.order_addr, o.submit_time, o.disp_time, "
						+ "o.conf_time FROM bmuser u, bmorder o WHERE u.id = o.user_id");
		if (criteria.containsKey(Constants.SEARCH_ORDER_ID)) {
			sql.append(" AND o.id = ?");
		}
		if (criteria.containsKey(Constants.SEARCH_USER)) {
			sql.append(" AND u.act_name = ?");
		}
		if (criteria.containsKey(Constants.SEARCH_ORDER_STATUS)) {
			sql.append(" AND o.status = ?");
		}
		if (criteria.containsKey(Constants.SEARCH_DATE)) {
			sql.append(" AND o.submit >= ? AND o.submit <= ?");
		}
		if (criteria.containsKey(Constants.SEARCH_TITLE)) {
			sql.append(" AND EXISTS (SELECT * FROM order_book ob, book b "
					+ "WHERE b.title = ? AND b.id = ob.book_id AND o.id = ob.order_id)");
		}
		sql.append(" ORDER BY o.id");
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql.toString());
			int count = 1;
			if (criteria.containsKey(Constants.SEARCH_ORDER_ID)) {
				pstmt.setInt(count,
						(Integer) criteria.get(Constants.SEARCH_ORDER_ID));
				++count;
			}

			if (criteria.containsKey(Constants.SEARCH_USER)) {
				pstmt.setString(count,
						(String) criteria.get(Constants.SEARCH_USER));
				++count;
			}

			if (criteria.containsKey(Constants.SEARCH_ORDER_STATUS)) {
				pstmt.setInt(count,
						(Integer) criteria.get(Constants.SEARCH_ORDER_STATUS));
				++count;
			}

			if (criteria.containsKey(Constants.SEARCH_DATE)) {
				@SuppressWarnings("unchecked")
				List<Date> dates = (List<Date>) criteria
						.get(Constants.SEARCH_DATE);
				pstmt.setTimestamp(count, new Timestamp(dates.get(0).getTime()));
				++count;
				pstmt.setTimestamp(count, new Timestamp(dates.get(1).getTime()));
				++count;
			}

			if (criteria.containsKey(Constants.SEARCH_TITLE)) {
				pstmt.setString(count,
						(String) criteria.get(Constants.SEARCH_TITLE));
				++count;
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new OrderSearchListItem(new Order(rs.getInt(2), rs
						.getInt(3), rs.getBigDecimal(4), rs.getInt(5), rs
						.getString(6), rs.getTimestamp(7), rs.getTimestamp(8),
						rs.getTimestamp(9)), rs.getString(1)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public boolean failOrder(int orderID) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean result = true;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmorder SET status = ? WHERE id = ? AND status = ?");
			pstmt.setInt(1, Constants.ORDER_FAILED);
			pstmt.setInt(2, orderID);
			pstmt.setInt(3, Constants.ORDER_DISPATCHED);
			int modifiedRowCount = pstmt.executeUpdate();
			if (modifiedRowCount != 1) {
				result = false;
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public List<OrderSearchListItem> getAllOrders() throws SQLException {
		// TODO Auto-generated method stub
		List<OrderSearchListItem> list = new ArrayList<OrderSearchListItem>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT u.act_name, o.id, o.user_id, "
					+ "o.total_charge, o.status, o.order_addr, o.submit_time, o.disp_time, "
					+ "o.conf_time FROM bmuser u, bmorder o WHERE u.id = o.user_id");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new OrderSearchListItem(new Order(rs.getInt(2), rs
						.getInt(3), rs.getBigDecimal(4), rs.getInt(5), rs
						.getString(6), rs.getTimestamp(7), rs.getTimestamp(8),
						rs.getTimestamp(9)), rs.getString(1)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}
}
